package com.briup.jdbc;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

/**
 * 用两种方式插入10000条数据
 */
public class PrecompileTest {
    private String driver =  "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://192.168.179.11:3306/briup?useServerPrepStmts=true&rewriteBatchedStatements=true";
    private String user = "root";
    private String password = "root";

    private long start;
    private long end;

    @Test
    public void statementTest() throws Exception {
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);
        Statement statement = conn.createStatement();
        for (int i = 4; i <= 10000; i++) {
            String sql = "insert into t_user values("+i+",'tom"+i+"',20)";
            statement.execute(sql);
        }
        statement.close();
        conn.close();
    }
    // 预编译+批处理+事务 = 提升效率
    @Test
    public void preparedStatementTest() throws Exception {
        Class.forName(driver);
        Connection connection = DriverManager.getConnection(url, user, password);
        // 关闭事务的自动提交
        connection.setAutoCommit(false);
        String sql = "insert into t_user values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for(int i = 20001; i <= 30000; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "tom"+i);
            preparedStatement.setInt(3, 20);
            preparedStatement.addBatch();
            if(i%2000 ==0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
                connection.commit();
            }
        }
        // 除的尽还好说，全部提交，如果除不尽，需要在循环外再提交一次
        // preparedStatement.executeBatch();
        connection.commit();
        preparedStatement.close();
        connection.close();
    }
    @Before
    public void before(){
        start = System.currentTimeMillis();
        System.out.println("开始前的时间："+start);
    }
    @After
    public void after(){
        end = System.currentTimeMillis();
        System.out.println("总耗时："+(end-start));
    }
}
